package com.favccxx.search.domain.datamapper.vertical;

import com.favccxx.search.domain.datamapper.DataMapper;
import com.favccxx.search.domain.datamapper.UnionEmptyKey;
import com.favccxx.search.domain.datamapper.VerticalDataSetMapper;
import com.favccxx.search.infrastructure.util.jackson.JacksonUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.codec.binary.Base64;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author favccxx
 * @date 5/13/2020 02:08 PM
 */
@Slf4j
public class VerticalMapperUtil {

    public static final String SELECT_MAP_KEY = "SELECT-";
    public static final String WHERE_MAP_KEY = "WHERE-";

    /**
     * Generate sql by mapper for vertical table
     *
     * @param verticalMapper
     * @return
     */
    public static String buildVerticalMapperSql(DataMapper verticalMapper) {
        String verticalSql = "";

        if (verticalMapper == null) {
            return verticalSql;
        }

        List<VerticalDataSetMapper> list = verticalMapper.getMapperFields();
        if (CollectionUtils.isEmpty(list)) {
            return verticalSql;
        }

        if (list.size() == 1) {
            Object obj = list.get(0);
            VerticalDataSetMapper verticalDataSetMapper = JacksonUtil.fromJson(JacksonUtil.toJsonString(obj), VerticalDataSetMapper.class);
            String dataSourceCode = verticalDataSetMapper.getDataSourceCode();
            String dataSetCode = verticalDataSetMapper.getDataSetCode();
            UnionEmptyKey unionEmptyKey = verticalDataSetMapper.getUnionEmptyKey();

            String fulSearchSql = VerticalMapperUtil.buildFullSearchSql(dataSourceCode, dataSetCode, verticalDataSetMapper.getFullSearchFields());
            Map<String, String> unionFields = verticalDataSetMapper.getUnionFields();
            Map<String, String> unionMap = VerticalMapperUtil.buildUnionSearch(dataSourceCode, dataSetCode, unionFields);

            List<String> unionKeyFileds = new ArrayList<>(unionFields.keySet());
            int length = unionKeyFileds.size();
            for (int i = 0; i < length; i++) {
                String unionKey = unionKeyFileds.get(i);
                StringBuffer bb = new StringBuffer();
                for (int j = 0; j < length; j++) {
                    String secKey = unionKeyFileds.get(j);
                    if (secKey.equals(unionKey)) {
                        bb.append(unionMap.get(SELECT_MAP_KEY + secKey));
                    } else {
                        if (unionEmptyKey != null && StringUtils.isNotBlank(unionEmptyKey.getType()) && StringUtils.isNotBlank(unionEmptyKey.getValue())) {
                            if ("String".equalsIgnoreCase(unionEmptyKey.getType())) {
                                bb.append(" ").append(unionEmptyKey.getValue()).append(" as ").append(secKey);
                            } else if ("Int".equalsIgnoreCase(unionEmptyKey.getType())) {
                                bb.append(" ").append(Integer.valueOf(unionEmptyKey.getValue())).append(" as ").append(secKey);
                            }
                        } else {
                            bb.append(" 0 as ").append(secKey);
                        }
                    }

                    if (j + 1 < length) {
                        bb.append(",");
                    }
                }

                StringBuffer subSb = new StringBuffer();
                subSb.append("Select ")
                        .append(fulSearchSql)
                        .append(bb)
                        .append(" From ")
                        .append("\"").append(dataSourceCode).append("\".\"").append(dataSetCode).append("\"")
                        .append(unionMap.get(WHERE_MAP_KEY + unionKey));

                if (i + 1 < length) {
                    subSb.append(" union all ");
                }

                verticalSql = verticalSql + subSb.toString();
            }

            log.info("sub sql: " + verticalSql);
            return verticalSql;
        }


        return verticalSql;
    }

    /**
     * Generate full search sql clause
     *
     * @param dataSourceCode
     * @param dataSetCode
     * @param fullFields
     * @return sql clause, like "db"."table"."field" as fieldA, "db"."table"."field" as fieldA as fieldB,
     */
    private static String buildFullSearchSql(String dataSourceCode, String dataSetCode, Map<String, String> fullFields) {
        StringBuffer sb = new StringBuffer();
        for (String fullKey : fullFields.keySet()) {
            String fullValue = fullFields.get(fullKey);
            if (fullValue.indexOf("(") > 0) {
                String fullValueTxt = fullValue.substring(fullValue.indexOf("(") + 1, fullValue.lastIndexOf(")"));
                String[] fields = fullValueTxt.split(",");

                for (int i = 0; i < fields.length; i++) {
                    String fieldKey = fields[i].trim();
                    if (StringUtils.isNotBlank(fieldKey)) {
                        String newKey = "\"" + dataSourceCode + "\".\"" + dataSetCode + "\".\"" + fieldKey + "\"";
                        fullValue = fullValue.replaceAll(fieldKey, newKey);
                    }
                }

                sb.append(" ").append(fullValue).append(" as ").append(fullKey).append(",");
            } else {
                sb.append(" \"").append(dataSourceCode).append("\".\"")
                        .append(dataSetCode).append("\".\"")
                        .append(fullFields.get(fullKey)).append("\" as ")
                        .append(fullKey).append(",");
            }

        }
        return sb.toString();
    }


    /**
     * @param dataSourceCode
     * @param dataSetCode
     * @param unionFields
     * @return
     */
    private static Map<String, String> buildUnionSearch(String dataSourceCode, String dataSetCode, Map<String, String> unionFields) {
        List<String> unionKeyFileds = new ArrayList<>(unionFields.keySet());
        Map<String, String> paramMap = new HashMap<>();
        for (String key : unionKeyFileds) {
            String unionRule = unionFields.get(key);
            String[] valurArr = unionRule.split(":");
            String caseCondition = valurArr[0];
            String originValue = valurArr[1];
            String[] caseArr = caseCondition.split("=");
            String originName = caseArr[0];
            String caseValue = caseArr[1];

            StringBuffer selectSb = new StringBuffer();
            selectSb.append(" \"")
                    .append(dataSourceCode).append("\".\"")
                    .append(dataSetCode).append("\".\"")
                    .append(originValue).append("\" as ").append(key);
            String selectFiled = selectSb.toString();
            log.debug("selectFiled: " + selectFiled);
            paramMap.put(SELECT_MAP_KEY + key, selectFiled);

            StringBuffer whereSb = new StringBuffer();
            whereSb.append(" where \"")
                    .append(dataSourceCode).append("\".\"")
                    .append(dataSetCode).append("\".\"")
                    .append(originName)
                    .append("\" = CAST(")
                    .append("\"dispersed\"")
                    .append(" AS BINARY)");
            String condition = whereSb.toString();
            log.debug("condition: " + condition);
            paramMap.put(WHERE_MAP_KEY + key, condition);
        }
        return paramMap;
    }

}
